using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace Forecast.Database
{
    public class Save
    {
        #region Variables

        private string _connectionString;

        #endregion
        #region Constructors

        // Forces use of overload
        private Save() { }

        public Save(string databaseConnectionString)
        {
            _connectionString = databaseConnectionString;
        }

        #endregion
        #region Interface

        #region Delete

        public void DeleteHolding(int holdingId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + Holding.TABLE_NAME + "] where [" + Holding.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.ID.ToString(), OleDbType.Integer).Value = holdingId;
                command.ExecuteNonQuery();
            }
        }

        public void DeleteMedal(int medalId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + Medal.TABLE_NAME + "] where [" + Medal.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Medal.ColumnName.ID.ToString(), OleDbType.Integer).Value = medalId;
                command.ExecuteNonQuery();
            }
        }

        public void DeleteNewsItem(int newsItemId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + NewsItem.TABLE_NAME + "] where [" + NewsItem.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(NewsItem.ColumnName.ID.ToString(), OleDbType.Integer).Value = newsItemId;
                command.ExecuteNonQuery();
            }
        }

        public void DeleteOffer(int offerId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + Offer.TABLE_NAME + "] where [" + Offer.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Offer.ColumnName.ID.ToString(), OleDbType.Integer).Value = offerId;
                command.ExecuteNonQuery();
            }
        }

        public void DeletePlayer(int playerId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + Player.TABLE_NAME + "] where [" + Player.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Player.ColumnName.ID.ToString(), OleDbType.Integer).Value = playerId;
                command.ExecuteNonQuery();
            }
        }

        public void DeletePrediction(int predictionId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + Prediction.TABLE_NAME + "] where [" + Prediction.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Integer).Value = predictionId;
                command.ExecuteNonQuery();
            }
        }

        public void DeletePredictionResult(int predictionResultId)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "delete from [" + PredictionResult.TABLE_NAME + "] where [" + PredictionResult.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Integer).Value = predictionResultId;
                command.ExecuteNonQuery();
            }
        }

        #endregion
        #region New

        public int NewHolding(Holding holding)
        {
            int newHoldingId = -1;
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + Holding.TABLE_NAME + "] ([" 
                    + Holding.ColumnName.Num_predictions.ToString() + "], [" 
                    + Holding.ColumnName.Player_id.ToString() + "], [" 
                    + Holding.ColumnName.Prediction_id.ToString() 
                    + "]) values (?,?,?)";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = holding.NumPredictions;
                command.Parameters.Add(Holding.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = holding.PlayerId;
                command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = holding.PredictionId;
                command.ExecuteNonQuery();

                sql = "select [" + Holding.ColumnName.ID.ToString() + "] from [" + Holding.TABLE_NAME + "] where [" + Holding.ColumnName.Player_id.ToString() + "] = ? and [" + Holding.ColumnName.Prediction_id.ToString() + "] = ?";
                command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = holding.PlayerId;
                command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = holding.PredictionId;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        newHoldingId = (Int32)reader[Holding.ColumnName.ID.ToString()];
                    }
                }
            }
            return newHoldingId;
        }

        public void NewMedal(Medal medal)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + Medal.TABLE_NAME + "] (["
                    + Medal.ColumnName.Medal.ToString() + "], ["
                    + Medal.ColumnName.Description.ToString()
                    + "]) values (?,?)";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Medal.ColumnName.Medal.ToString(), OleDbType.VarWChar).Value = medal.Name;
                command.Parameters.Add(Medal.ColumnName.Description.ToString(), OleDbType.VarWChar).Value = medal.Description;
                command.ExecuteNonQuery();
            }
        }

        public void NewNewsItem(NewsItem newsItem)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + NewsItem.TABLE_NAME + "] ([" 
                    + NewsItem.ColumnName.News_date.ToString() + "], [" 
                    + NewsItem.ColumnName.News_message.ToString() + "], [" 
                    + NewsItem.ColumnName.Player_id.ToString() + "], [" 
                    + NewsItem.ColumnName.Prediction_id.ToString() + "], ["
                    + NewsItem.ColumnName.Title.ToString() + "], [" 
                    + NewsItem.ColumnName.Should_be_shown_in_main_news.ToString()
                    + "]) values (?,?,?,?,?,?)";

                object playerId = DBNull.Value;
                if (newsItem.PlayerId > 0) playerId = newsItem.PlayerId;
                object predictionId = DBNull.Value;
                if (newsItem.PredictionId > 0) predictionId = newsItem.PredictionId;

                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(NewsItem.ColumnName.News_date.ToString(), OleDbType.Date).Value = newsItem.NewsDate;
                command.Parameters.Add(NewsItem.ColumnName.News_message.ToString(), OleDbType.VarWChar).Value = newsItem.Message;
                command.Parameters.Add(NewsItem.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = playerId;
                command.Parameters.Add(NewsItem.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = predictionId;
                command.Parameters.Add(NewsItem.ColumnName.Title.ToString(), OleDbType.VarWChar).Value = newsItem.Title;
                command.Parameters.Add(NewsItem.ColumnName.Should_be_shown_in_main_news.ToString(), OleDbType.Boolean).Value = newsItem.ShouldBeShownInMainNews;
                command.ExecuteNonQuery();
            }
        }

        public void NewOffer(Offer offer)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + Offer.TABLE_NAME + "] (["
                    + Offer.ColumnName.Acceptor.ToString() + "], ["
                    + Offer.ColumnName.End_date.ToString() + "], ["
                    + Offer.ColumnName.Is_accepted.ToString() + "], ["
                    + Offer.ColumnName.Is_active.ToString() + "], ["
                    + Offer.ColumnName.Is_buy.ToString() + "], ["
                    + Offer.ColumnName.Num_predictions.ToString() + "], ["
                    + Offer.ColumnName.Player_id.ToString() + "], ["
                    + Offer.ColumnName.Prediction_id.ToString() + "], ["
                    + Offer.ColumnName.Price.ToString()
                    + "]) values (?,?,?,?,?,?,?,?,?)";
                OleDbCommand command = new OleDbCommand(sql, connection);

                object acceptor = DBNull.Value;
                if (offer.AcceptorId > 0) acceptor = offer.AcceptorId;

                command.Parameters.Add(Offer.ColumnName.Acceptor.ToString(), OleDbType.Integer).Value = acceptor;
                command.Parameters.Add(Offer.ColumnName.End_date.ToString(), OleDbType.Date).Value = offer.EndDate;
                command.Parameters.Add(Offer.ColumnName.Is_accepted.ToString(), OleDbType.Boolean).Value = offer.IsAccepted;
                command.Parameters.Add(Offer.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = offer.IsActive;
                command.Parameters.Add(Offer.ColumnName.Is_buy.ToString(), OleDbType.Boolean).Value = offer.IsBuy;
                command.Parameters.Add(Offer.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = offer.NumPredictions;
                command.Parameters.Add(Offer.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = offer.PlayerId;
                command.Parameters.Add(Offer.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = offer.PredictionId;
                command.Parameters.Add(Offer.ColumnName.Price.ToString(), OleDbType.Integer).Value = offer.Price;
                command.ExecuteNonQuery();
            }
        }        

        public int NewPlayer(Player player)
        {
            int newPlayerId = -1;
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + Player.TABLE_NAME + "] ([" 
                    + Player.ColumnName.User_name.ToString() + "], [" 
                    + Player.ColumnName.Cash.ToString() 
                    + "]) VALUES (?,?)";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Player.ColumnName.User_name.ToString(), OleDbType.VarWChar).Value = player.UserName;
                command.Parameters.Add(Player.ColumnName.Cash.ToString(), OleDbType.Integer).Value = player.Cash;
                command.ExecuteNonQuery();

                sql = "select [" + Player.ColumnName.ID.ToString() + "] from [" + Player.TABLE_NAME + "] where [" + Player.ColumnName.User_name.ToString() + "] = ?";
                command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Player.ColumnName.User_name.ToString(), OleDbType.VarWChar).Value = player.UserName;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        newPlayerId = (Int32)reader[Player.ColumnName.ID.ToString()];
                    }
                }
            }
            return newPlayerId;
        }

        public int NewPrediction(Prediction prediction)
        {
            int newPredictionId = -1;
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + Prediction.TABLE_NAME + "] (["
                    + Prediction.ColumnName.End_date.ToString() + "], ["
                    + Prediction.ColumnName.Has_succeeded.ToString() + "], ["
                    + Prediction.ColumnName.Is_active.ToString() + "], ["
                    + Prediction.ColumnName.Is_closed.ToString() + "], ["
                    + Prediction.ColumnName.Last_change.ToString() + "], ["
                    + Prediction.ColumnName.Last_value.ToString() + "], ["
                    + Prediction.ColumnName.Long_description.ToString() + "], ["
                    + Prediction.ColumnName.Short_description.ToString() + "], ["
                    + Prediction.ColumnName.Ticker.ToString()
                    + "]) values (?,?,?,?,?,?,?,?,?)";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.End_date.ToString(), OleDbType.Date).Value = prediction.EndDate;
                command.Parameters.Add(Prediction.ColumnName.Has_succeeded.ToString(), OleDbType.Boolean).Value = prediction.HasSucceeded;
                command.Parameters.Add(Prediction.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = prediction.IsActive;
                command.Parameters.Add(Prediction.ColumnName.Is_closed.ToString(), OleDbType.Boolean).Value = prediction.IsClosed;
                command.Parameters.Add(Prediction.ColumnName.Last_change.ToString(), OleDbType.Double).Value = prediction.LastChange;
                command.Parameters.Add(Prediction.ColumnName.Last_value.ToString(), OleDbType.Integer).Value = prediction.LastValue;
                command.Parameters.Add(Prediction.ColumnName.Long_description.ToString(), OleDbType.VarWChar).Value = prediction.LongDescription;
                command.Parameters.Add(Prediction.ColumnName.Short_description.ToString(), OleDbType.VarWChar).Value = prediction.ShortDescription;
                command.Parameters.Add(Prediction.ColumnName.Ticker.ToString(), OleDbType.VarWChar).Value = prediction.Ticker;
                command.ExecuteNonQuery();

                sql = "select [" + Prediction.ColumnName.ID.ToString() + "] from [" + Prediction.TABLE_NAME + "] where [" + Prediction.ColumnName.Ticker.ToString() + "] = ?";
                command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Prediction.ColumnName.Ticker.ToString(), OleDbType.VarWChar).Value = prediction.Ticker;
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        newPredictionId = (Int32)reader[Player.ColumnName.ID.ToString()];
                    }
                }
            }
            return newPredictionId;
        }

        public void NewPredictionResult(PredictionResult predictionResult)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "insert into [" + PredictionResult.TABLE_NAME + "] (["
                    + PredictionResult.ColumnName.Cash_earned.ToString() + "], ["
                    + PredictionResult.ColumnName.Medal_id.ToString() + "], ["
                    + PredictionResult.ColumnName.Num_trades.ToString() + "], ["
                    + PredictionResult.ColumnName.Player_id.ToString() + "], ["
                    + PredictionResult.ColumnName.Prediction_id.ToString()
                    + "]) values (?,?,?,?,?)";

                object medal = DBNull.Value;
                if (predictionResult.MedalId > 0) medal = predictionResult.MedalId;
                
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(PredictionResult.ColumnName.Cash_earned.ToString(), OleDbType.Integer).Value = predictionResult.CashEarned;
                command.Parameters.Add(PredictionResult.ColumnName.Medal_id.ToString(), OleDbType.Integer).Value = medal;
                command.Parameters.Add(PredictionResult.ColumnName.Num_trades.ToString(), OleDbType.Integer).Value = predictionResult.NumTrades;
                command.Parameters.Add(PredictionResult.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = predictionResult.PlayerId;
                command.Parameters.Add(PredictionResult.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = predictionResult.PredictionId;
                command.ExecuteNonQuery();
            }
        }


        #endregion
        #region Edit

        public void EditHolding(Holding holding)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + Holding.TABLE_NAME + "] set ["
                    + Holding.ColumnName.Num_predictions.ToString() + "] = ?, ["
                    + Holding.ColumnName.Player_id.ToString() + "] = ?, ["
                    + Holding.ColumnName.Prediction_id.ToString()
                    + "] = ? where [" + Holding.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Holding.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = holding.NumPredictions;
                command.Parameters.Add(Holding.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = holding.PlayerId;
                command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = holding.PredictionId;
                command.Parameters.Add(Holding.ColumnName.ID.ToString(), OleDbType.Integer).Value = holding.Id;
                command.ExecuteNonQuery();
            }
        }

        public void EditMedal(Medal medal)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + Medal.TABLE_NAME + "] set ["
                    + Medal.ColumnName.Medal.ToString() + "] = ?, ["
                    + Medal.ColumnName.Description.ToString()
                    + "] = ? where [" + Medal.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Medal.ColumnName.Medal.ToString(), OleDbType.VarWChar).Value = medal.Name;
                command.Parameters.Add(Medal.ColumnName.Description.ToString(), OleDbType.VarWChar).Value = medal.Description;
                command.ExecuteNonQuery();
            }
        }

        public void EditNewsItem(NewsItem newsItem)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + NewsItem.TABLE_NAME + "] set ["
                    + NewsItem.ColumnName.News_date.ToString() + "] = ?, ["
                    + NewsItem.ColumnName.News_message.ToString() + "] = ?, ["
                    + NewsItem.ColumnName.Player_id.ToString() + "] = ?, ["
                    + NewsItem.ColumnName.Prediction_id.ToString() + "] = ?, ["
                    + NewsItem.ColumnName.Title.ToString()
                    + "] = ? where [" + NewsItem.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);

                object playerId = DBNull.Value;
                if (newsItem.PlayerId > 0) playerId = newsItem.PlayerId;
                object predictionId = DBNull.Value;
                if (newsItem.PredictionId > 0) predictionId = newsItem.PredictionId;

                command.Parameters.Add(NewsItem.ColumnName.News_date.ToString(), OleDbType.Date).Value = newsItem.NewsDate;
                command.Parameters.Add(NewsItem.ColumnName.News_message.ToString(), OleDbType.VarWChar).Value = newsItem.Message;
                command.Parameters.Add(NewsItem.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = playerId;
                command.Parameters.Add(NewsItem.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = predictionId;
                command.Parameters.Add(NewsItem.ColumnName.Title.ToString(), OleDbType.VarWChar).Value = newsItem.Title;
                command.Parameters.Add(NewsItem.ColumnName.ID.ToString(), OleDbType.Integer).Value = newsItem.Id;
                command.ExecuteNonQuery();
            }
        }

        public void EditOffer(Offer offer)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + Offer.TABLE_NAME + "] set ["
                    + Offer.ColumnName.Acceptor.ToString() + "] = ?, ["
                    + Offer.ColumnName.End_date.ToString() + "] = ?, ["
                    + Offer.ColumnName.Is_accepted.ToString() + "] = ?, ["
                    + Offer.ColumnName.Is_active.ToString() + "] = ?, ["
                    + Offer.ColumnName.Is_buy.ToString() + "] = ?, ["
                    + Offer.ColumnName.Num_predictions.ToString() + "] = ?, ["
                    + Offer.ColumnName.Player_id.ToString() + "] = ?, ["
                    + Offer.ColumnName.Prediction_id.ToString() + "] = ?, ["
                    + Offer.ColumnName.Price.ToString()
                    + "] = ? where [" + Offer.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);

                object acceptor = DBNull.Value;
                if (offer.AcceptorId > 0) acceptor = offer.AcceptorId;

                command.Parameters.Add(Offer.ColumnName.Acceptor.ToString(), OleDbType.Integer).Value = acceptor;
                command.Parameters.Add(Offer.ColumnName.End_date.ToString(), OleDbType.Date).Value = offer.EndDate;
                command.Parameters.Add(Offer.ColumnName.Is_accepted.ToString(), OleDbType.Boolean).Value = offer.IsAccepted;
                command.Parameters.Add(Offer.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = offer.IsActive;
                command.Parameters.Add(Offer.ColumnName.Is_buy.ToString(), OleDbType.Boolean).Value = offer.IsBuy;
                command.Parameters.Add(Offer.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = offer.NumPredictions;
                command.Parameters.Add(Offer.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = offer.PlayerId;
                command.Parameters.Add(Offer.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = offer.PredictionId;
                command.Parameters.Add(Offer.ColumnName.Price.ToString(), OleDbType.Integer).Value = offer.Price;
                command.Parameters.Add(Offer.ColumnName.ID.ToString(), OleDbType.Integer).Value = offer.Id;
                command.ExecuteNonQuery();
            }
        }

        public void EditPlayer(Player player)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + Player.TABLE_NAME + "] set [" 
                    + Player.ColumnName.User_name.ToString() + "] = ?, [" 
                    + Player.ColumnName.Cash.ToString() 
                    + "] = ? where [" + Player.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(Player.ColumnName.User_name.ToString(), OleDbType.VarWChar).Value = player.UserName;
                command.Parameters.Add(Player.ColumnName.Cash.ToString(), OleDbType.Integer).Value = player.Cash;
                command.Parameters.Add(Player.ColumnName.ID.ToString(), OleDbType.Integer).Value = player.Id;
                command.ExecuteNonQuery();
            }
        }

        public void EditPrediction(Prediction prediction)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + Prediction.TABLE_NAME + "] set ["
                    + Prediction.ColumnName.End_date.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Has_succeeded.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Is_active.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Is_closed.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Last_change.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Last_value.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Long_description.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Short_description.ToString() + "] = ?, ["
                    + Prediction.ColumnName.Ticker.ToString()
                    + "] = ? where [" + Prediction.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);

                command.Parameters.Add(Prediction.ColumnName.End_date.ToString(), OleDbType.Date).Value = prediction.EndDate;
                command.Parameters.Add(Prediction.ColumnName.Has_succeeded.ToString(), OleDbType.Boolean).Value = prediction.HasSucceeded;
                command.Parameters.Add(Prediction.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = prediction.IsActive;
                command.Parameters.Add(Prediction.ColumnName.Is_closed.ToString(), OleDbType.Boolean).Value = prediction.IsClosed;
                command.Parameters.Add(Prediction.ColumnName.Last_change.ToString(), OleDbType.Double).Value = prediction.LastChange;
                command.Parameters.Add(Prediction.ColumnName.Last_value.ToString(), OleDbType.Integer).Value = prediction.LastValue;
                command.Parameters.Add(Prediction.ColumnName.Long_description.ToString(), OleDbType.VarWChar).Value = prediction.LongDescription;
                command.Parameters.Add(Prediction.ColumnName.Short_description.ToString(), OleDbType.VarWChar).Value = prediction.ShortDescription;
                command.Parameters.Add(Prediction.ColumnName.Ticker.ToString(), OleDbType.VarWChar).Value = prediction.Ticker;
                command.Parameters.Add(Prediction.ColumnName.ID.ToString(), OleDbType.Integer).Value = prediction.Id;
                command.ExecuteNonQuery();
            }
        }

        public void EditPredictionResult(PredictionResult predictionResult)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                string sql = "update [" + PredictionResult.TABLE_NAME + "] set ["
                    + PredictionResult.ColumnName.Cash_earned.ToString() + "] = ?, ["
                    + PredictionResult.ColumnName.Medal_id.ToString() + "] = ?, ["
                    + PredictionResult.ColumnName.Num_trades.ToString() + "] = ?, ["
                    + PredictionResult.ColumnName.Player_id.ToString() + "] = ?, ["
                    + PredictionResult.ColumnName.Prediction_id.ToString() + "] = ?, ["
                    + "] = ? where [" + PredictionResult.ColumnName.ID.ToString() + "] = ?";
                OleDbCommand command = new OleDbCommand(sql, connection);
                command.Parameters.Add(PredictionResult.ColumnName.Cash_earned.ToString(), OleDbType.Integer).Value = predictionResult.CashEarned;
                command.Parameters.Add(PredictionResult.ColumnName.Medal_id.ToString(), OleDbType.Integer).Value = predictionResult.MedalId;
                command.Parameters.Add(PredictionResult.ColumnName.Num_trades.ToString(), OleDbType.Integer).Value = predictionResult.NumTrades;
                command.Parameters.Add(PredictionResult.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = predictionResult.PlayerId;
                command.Parameters.Add(PredictionResult.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = predictionResult.PredictionId;
                command.ExecuteNonQuery();
            }
        }

        #endregion
        #region Edit multiple

        public void EditPlayers(IList<Player> players)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();

                foreach (Player player in players)
                {
                    string sql = "update [" + Player.TABLE_NAME + "] set ["
                        + Player.ColumnName.User_name.ToString() + "] = ?, ["
                        + Player.ColumnName.Cash.ToString()
                        + "] = ? where [" + Player.ColumnName.ID.ToString() + "] = ?";
                    OleDbCommand command = new OleDbCommand(sql, connection);
                    command.Parameters.Add(Player.ColumnName.User_name.ToString(), OleDbType.VarWChar).Value = player.UserName;
                    command.Parameters.Add(Player.ColumnName.Cash.ToString(), OleDbType.Integer).Value = player.Cash;
                    command.Parameters.Add(Player.ColumnName.ID.ToString(), OleDbType.Integer).Value = player.Id;
                    command.ExecuteNonQuery();
                }
            }
        }

        public void EditHoldings(IList<Holding> holdings)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                foreach (Holding holding in holdings)
                {
                    if (holding.Id == -1)
                    {
                        string sql = "insert into [" + Holding.TABLE_NAME + "] (["
                            + Holding.ColumnName.Num_predictions.ToString() + "], ["
                            + Holding.ColumnName.Player_id.ToString() + "], ["
                            + Holding.ColumnName.Prediction_id.ToString()
                            + "]) values (?,?,?)";
                        OleDbCommand command = new OleDbCommand(sql, connection);
                        command.Parameters.Add(Holding.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = holding.NumPredictions;
                        command.Parameters.Add(Holding.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = holding.PlayerId;
                        command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = holding.PredictionId;
                        command.ExecuteNonQuery();
                    }
                    else
                    {
                        string sql = "update [" + Holding.TABLE_NAME + "] set ["
                            + Holding.ColumnName.Num_predictions.ToString() + "] = ?, ["
                            + Holding.ColumnName.Player_id.ToString() + "] = ?, ["
                            + Holding.ColumnName.Prediction_id.ToString()
                            + "] = ? where [" + Holding.ColumnName.ID.ToString() + "] = ?";
                        OleDbCommand command = new OleDbCommand(sql, connection);
                        command.Parameters.Add(Holding.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = holding.NumPredictions;
                        command.Parameters.Add(Holding.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = holding.PlayerId;
                        command.Parameters.Add(Holding.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = holding.PredictionId;
                        command.Parameters.Add(Holding.ColumnName.ID.ToString(), OleDbType.Integer).Value = holding.Id;
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public void EditOffers(IList<Offer> offers)
        {
            using (OleDbConnection connection = new OleDbConnection(_connectionString))
            {
                connection.Open();
                foreach (Offer offer in offers)
                {
                    string sql = "update [" + Offer.TABLE_NAME + "] set ["
                        + Offer.ColumnName.Acceptor.ToString() + "] = ?, ["
                        + Offer.ColumnName.End_date.ToString() + "] = ?, ["
                        + Offer.ColumnName.Is_accepted.ToString() + "] = ?, ["
                        + Offer.ColumnName.Is_active.ToString() + "] = ?, ["
                        + Offer.ColumnName.Is_buy.ToString() + "] = ?, ["
                        + Offer.ColumnName.Num_predictions.ToString() + "] = ?, ["
                        + Offer.ColumnName.Player_id.ToString() + "] = ?, ["
                        + Offer.ColumnName.Prediction_id.ToString() + "] = ?, ["
                        + Offer.ColumnName.Price.ToString()
                        + "] = ? where [" + Offer.ColumnName.ID.ToString() + "] = ?";
                    OleDbCommand command = new OleDbCommand(sql, connection);

                    object acceptor = DBNull.Value;
                    if (offer.AcceptorId > 0) acceptor = offer.AcceptorId;

                    command.Parameters.Add(Offer.ColumnName.Acceptor.ToString(), OleDbType.Integer).Value = acceptor;
                    command.Parameters.Add(Offer.ColumnName.End_date.ToString(), OleDbType.Date).Value = offer.EndDate;
                    command.Parameters.Add(Offer.ColumnName.Is_accepted.ToString(), OleDbType.Boolean).Value = offer.IsAccepted;
                    command.Parameters.Add(Offer.ColumnName.Is_active.ToString(), OleDbType.Boolean).Value = offer.IsActive;
                    command.Parameters.Add(Offer.ColumnName.Is_buy.ToString(), OleDbType.Boolean).Value = offer.IsBuy;
                    command.Parameters.Add(Offer.ColumnName.Num_predictions.ToString(), OleDbType.Integer).Value = offer.NumPredictions;
                    command.Parameters.Add(Offer.ColumnName.Player_id.ToString(), OleDbType.Integer).Value = offer.PlayerId;
                    command.Parameters.Add(Offer.ColumnName.Prediction_id.ToString(), OleDbType.Integer).Value = offer.PredictionId;
                    command.Parameters.Add(Offer.ColumnName.Price.ToString(), OleDbType.Integer).Value = offer.Price;
                    command.Parameters.Add(Offer.ColumnName.ID.ToString(), OleDbType.Integer).Value = offer.Id;
                    command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #endregion
    }
}
